import os
import sqlite3
import sys
from typing import List, Dict, Any, Optional, Tuple
import logging

from utils.ConfigHelper import ConfigHelper


class StudentDB:
    """
    考生数据库管理类
    功能：
    - 自动检查并创建students表
    - 提供基本的CRUD操作
    - 支持批量插入
    - 自动管理数据库连接
    """

    def __init__(self,
                 config_file: Optional[str] = None,  # 改为可选参数
                 section: str = "db"):
        """
        初始化数据库连接并检查表结构

        :param db_path: 数据库文件路径
        """
        # 如果没有提供config_file，则使用默认路径
        if config_file is None:
            config_file = self._get_default_config_path()

        dbconfig = ConfigHelper(config_file)
        # 读取配置文件
        self.dbconfig = dbconfig
        self.db_path = self._get_default_config_db_path()
        #dbconfig.get_value(section, 'db_path')
        self.logger = logging.getLogger('StudentDB')
        logging.basicConfig(level=logging.INFO)
        self._connection = None

        # 初始化时自动检查并创建表
        self._initialize_database()

    def _get_default_config_path(self):
        """获取默认配置文件路径"""
        if getattr(sys, 'frozen', False):
            # 打包后exe的目录
            base_dir = os.path.dirname(sys.executable)
        else:
            # 开发环境中的目录
            base_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

        return os.path.join(base_dir, "config", "apiconfig.ini")

    def _get_default_config_db_path(self):
        """获取默认配置文件路径"""
        if getattr(sys, 'frozen', False):
            # 打包后exe的目录
            base_dir = os.path.dirname(sys.executable)
        else:
            # 开发环境中的目录
            base_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

        return os.path.join(base_dir, "db", "examFpl")

    def _initialize_database(self):
        """初始化数据库，确保表存在"""
        try:
            self.connect()

            # 检查表是否存在
            if not self._table_exists('students'):
                self._create_students_table()
                self.logger.info("成功创建students表")
            else:
                self.logger.info("students表已存在")

        except Exception as e:
            self.logger.error(f"数据库初始化失败: {e}")
            raise

    def _table_exists(self, table_name: str) -> bool:
        """检查表是否存在"""
        query = "SELECT name FROM sqlite_master WHERE type='table' AND name=?"
        cursor = self._connection.cursor()
        cursor.execute(query, (table_name,))
        result = cursor.fetchone()
        cursor.close()
        return result is not None

    def _create_students_table(self):
        """创建students表"""
        create_table_sql = """
        CREATE TABLE students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            testId TEXT NOT NULL,
            testName TEXT,
            siteCode TEXT,
            siteName TEXT,
            deptCode TEXT,
            deptName TEXT,
            subjectCode TEXT,
            subjectName TEXT,
            gradeCode TEXT,
            gradeName TEXT,
            schoolCode TEXT,
            schoolName TEXT,
            classCode TEXT,
            className TEXT,
            itemCode TEXT,
            itemName TEXT,
            studentCode TEXT,
            studentName TEXT NOT NULL,
            gender INTEGER,
            sortName TEXT,
            idCard TEXT,
            studentType INTEGER,
            studentItemStatus INTEGER,
            scheduleId INTEGER,
            scheduleNo INTEGER,
            scheduleTime INTEGER,
            groupNo TEXT,
            trackNo TEXT,
            groupType INTEGER,
            photoURL TEXT,
            examType INTEGER,
            downId TEXT,
            status TEXT,
            resultId TEXT,
            result TEXT,
            mtEquipment TEXT,
            gmtStarted TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        """

        index_sqls = [
            "CREATE INDEX idx_students_testId ON students(testId);",
            "CREATE INDEX idx_students_studentCode ON students(studentCode);",
            "CREATE INDEX idx_students_idCard ON students(idCard);",
            "CREATE INDEX idx_students_idCard ON students(status);",
            "CREATE INDEX idx_students_studentName ON students(studentName);"
        ]

        cursor = self._connection.cursor()
        try:
            # 创建表
            cursor.execute(create_table_sql)

            # 创建索引
            for sql in index_sqls:
                cursor.execute(sql)

            self._connection.commit()
        except Exception as e:
            self._connection.rollback()
            raise
        finally:
            cursor.close()

    def connect(self):
        """建立数据库连接"""
        if self._connection is None:
            self._connection = sqlite3.connect(self.db_path)
            self._connection.row_factory = sqlite3.Row  # 启用字典式访问
            self.logger.info(f"已连接到数据库: {self.db_path}")

    def close(self):
        """关闭数据库连接"""
        if self._connection:
            self._connection.close()
            self._connection = None
            self.logger.info("数据库连接已关闭")

    def __enter__(self):
        """上下文管理器入口"""
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """上下文管理器退出"""
        self.close()

    def insert_student(self, student_data: Dict[str, Any]) -> int:
        """
        插入单个考生记录

        :param student_data: 考生数据字典
        :return: 插入的行ID
        """
        self._validate_student_data(student_data)

        columns = ', '.join(student_data.keys())
        placeholders = ', '.join(['?'] * len(student_data))
        sql = f"INSERT INTO students ({columns}) VALUES ({placeholders})"

        cursor = self._connection.cursor()
        try:
            cursor.execute(sql, tuple(student_data.values()))
            self._connection.commit()
            return cursor.lastrowid
        except Exception as e:
            self._connection.rollback()
            raise
        finally:
            cursor.close()

    def insert_students_batch(self, students_data: List[Dict[str, Any]]) -> int:
        """
        批量插入考生记录
        使用事务保证数据一致性，性能比单条插入高很多

        :param students_data: 考生数据字典列表
        :return: 成功插入的记录数
        """
        if not students_data:
            return 0

        # 验证所有数据
        for student in students_data:
            self._validate_student_data(student)

        # 获取所有字段的并集
        all_keys = set()
        for student in students_data:
            all_keys.update(student.keys())
        columns = ', '.join(all_keys)
        placeholders = ', '.join(['?'] * len(all_keys))

        # 准备SQL和数据
        sql = f"INSERT INTO students ({columns}) VALUES ({placeholders})"
        data_to_insert = []
        for student in students_data:
            row = tuple(student.get(key, None) for key in all_keys)
            data_to_insert.append(row)

        cursor = self._connection.cursor()
        try:
            # 使用executemany批量插入
            cursor.executemany(sql, data_to_insert)
            self._connection.commit()
            return cursor.rowcount
        except Exception as e:
            self._connection.rollback()
            self.logger.error(f"批量插入失败: {e}")
            raise
        finally:
            cursor.close()

    def _validate_student_data(self, student_data: Dict[str, Any]):
        """验证考生数据是否包含必填字段"""
        required_fields = ['testId', 'studentName']
        for field in required_fields:
            if field not in student_data:
                raise ValueError(f"缺少必填字段: {field}")

    def get_student_by_id(self, student_id: int) -> Optional[Dict[str, Any]]:
        """
        根据ID获取考生信息

        :param student_id: 考生ID
        :return: 考生信息字典或None
        """
        sql = "SELECT * FROM students WHERE id = ?"
        cursor = self._connection.cursor()
        try:
            cursor.execute(sql, (student_id,))
            row = cursor.fetchone()
            return dict(row) if row else None
        finally:
            cursor.close()

    def get_student_by_zkh(self, zkh: int) -> Optional[Dict[str, Any]]:
        """
        根据ID获取考生信息

        :param zkh: 准考证号
        :return: 考生信息字典或None
        """
        sql = "SELECT * FROM students WHERE studentCode = ?"
        cursor = self._connection.cursor()
        try:
            cursor.execute(sql, (zkh,))
            row = cursor.fetchone()
            return dict(row) if row else None
        finally:
            cursor.close()

    def get_student_by_zkh_item(self, zkh: int, item: str) -> Optional[Dict[str, Any]]:
        """
        根据ID获取考生信息

        :param zkh: 准考证号
        :return: 考生信息字典或None
        """
        sql = "SELECT * FROM students WHERE studentCode = ? and itemName = ?"
        cursor = self._connection.cursor()
        try:
            cursor.execute(sql, (zkh, item,))
            row = cursor.fetchone()
            return dict(row) if row else None
        finally:
            cursor.close()

    def get_students_by_zkh_groupNo(self,  group_no: Optional[str] = None) -> List[Dict[str, Any]]:
        """
        根据准考证号列表和组号获取考生信息，包括排球和跳绳项目

        :param studentCodes: 准考证号列表
        :param group_no: 组号(可选)
        :return: 包含考生信息和项目结果的字典列表
        """

        # Create placeholders for the IN clause
        # placeholders = ','.join(['?'] * len(studentCodes))
        sql = f"""
            SELECT 
                studentCode,
                studentName,
                schoolName,
                gender,
                groupNo,
                trackNo,
                MAX(CASE WHEN xcxm1 = '排球' THEN xcxm1 ELSE NULL END) AS xcxm1,
                MAX(CASE WHEN xcxm1 = '排球' THEN xcxm1Result ELSE NULL END) AS xcxm1Result,
                MAX(CASE WHEN xcxm2 = '跳绳' THEN xcxm2 ELSE NULL END) AS xcxm2,
                MAX(CASE WHEN xcxm2 = '跳绳' THEN xcxm2Result ELSE NULL END) AS xcxm2Result
            FROM (
                SELECT 
                    studentCode,
                    studentName,
                    schoolName,
                    gender,
                    groupNo,
                    trackNo,
                    itemName AS xcxm1,
                    result AS xcxm1Result,
                    NULL AS xcxm2,
                    NULL AS xcxm2Result
                FROM students
            WHERE itemName = '排球'
    
            UNION ALL
    
            SELECT 
                studentCode,
                studentName,
                schoolName,
                gender,
                groupNo,
                trackNo,
                NULL AS xcxm1,
                NULL AS xcxm1Result,
                itemName AS xcxm2,
                result AS xcxm2Result
            FROM students
            WHERE itemName = '跳绳'
        ) AS combined
        """
        # Add groupNo filter if provided
        params = []  # list(studentCodes)
        # if group_no is not None:
        sql += " where groupNo = ?"
        params.append(group_no)
        sql += """
        GROUP BY studentCode, studentName, gender, groupNo, trackNo
        """

        cursor = self._connection.cursor()
        try:
            cursor.execute(sql, params)
            rows = cursor.fetchall()
            return [dict(row) for row in rows]
        finally:
            cursor.close()

    def update_student(self, student_id: int, update_data: Dict[str, Any]) -> bool:
        """
        更新考生信息

        :param student_id: 考生ID
        :param update_data: 要更新的字段字典
        :return: 是否成功更新
        """
        if not update_data:
            return False

        set_clause = ', '.join([f"{k} = ?" for k in update_data.keys()])
        sql = f"UPDATE students SET {set_clause} WHERE id = ?"

        params = list(update_data.values())
        params.append(student_id)

        cursor = self._connection.cursor()
        try:
            cursor.execute(sql, params)
            self._connection.commit()
            return cursor.rowcount > 0
        except Exception as e:
            self._connection.rollback()
            raise
        finally:
            cursor.close()

    def delete_student(self, student_id: int) -> bool:
        """
        删除考生记录

        :param student_id: 考生ID
        :return: 是否成功删除
        """
        sql = "DELETE FROM students WHERE id = ?"
        cursor = self._connection.cursor()
        try:
            cursor.execute(sql, (student_id,))
            self._connection.commit()
            return cursor.rowcount > 0
        except Exception as e:
            self._connection.rollback()
            raise
        finally:
            cursor.close()

    def delete_all_student(self) -> bool:
        """
        删除所有考生记录

        :param student_id: 考生ID
        :return: 是否成功删除
        """
        sql = "DELETE FROM students"
        cursor = self._connection.cursor()
        try:
            cursor.execute(sql)
            self._connection.commit()
            return cursor.rowcount > 0
        except Exception as e:
            self._connection.rollback()
            raise
        finally:
            cursor.close()

    def search_students(self, conditions: Dict[str, Any], limit: int = 100) -> List[Dict[str, Any]]:
        """
        根据条件搜索考生（自动过滤空值条件）

        :param conditions: 查询条件字典（会自动过滤掉None和空字符串值）
        :param limit: 返回结果最大数量
        :return: 考生信息列表
        """
        # 过滤掉值为None或空字符串的条件
        valid_conditions = {
            k: v for k, v in conditions.items()
            if v is not None and v != ''
        }

        if not valid_conditions:
            sql = "SELECT * FROM students LIMIT ?"
            params = (limit,)
        else:
            where_clause = ' AND '.join([f"{k} = ?" for k in valid_conditions.keys()])
            sql = f"SELECT * FROM students WHERE {where_clause} LIMIT ?"
            params = list(valid_conditions.values()) + [limit]

        cursor = self._connection.cursor()
        try:
            cursor.execute(sql, params)
            return [dict(row) for row in cursor.fetchall()]
        finally:
            cursor.close()

    def search_students_with_pagination(
            self,
            conditions: Dict[str, Any],
            page_num: int = 1,
            page_size: int = 10
    ) -> Tuple[List[Dict[str, Any]], int]:
        """
        带分页的考生搜索方法

        :param conditions: 查询条件字典（自动过滤None和空字符串）
        :param page_num: 页码（从1开始）
        :param page_size: 每页数量
        :return: (考生列表, 总记录数)
        """
        # 过滤无效条件
        valid_conditions = {
            k: v for k, v in conditions.items()
            if v is not None and v != ''
        }

        # 1. 构建基础查询SQL
        where_clause = ""
        params = []
        if valid_conditions:
            where_clause = "WHERE " + " AND ".join([f"{k} = ?" for k in valid_conditions.keys()])
            params.extend(valid_conditions.values())

        # 2. 查询总记录数
        count_sql = f"SELECT COUNT(*) FROM students {where_clause}"

        # 3. 查询分页数据
        offset = (page_num - 1) * page_size
        data_sql = f"""
            SELECT * FROM students 
            {where_clause}
            LIMIT ? OFFSET ?
        """

        cursor = self._connection.cursor()
        try:
            # 获取总数
            cursor.execute(count_sql, params)
            total = cursor.fetchone()[0]

            # 获取分页数据
            cursor.execute(data_sql, params + [page_size, offset])
            students = [dict(row) for row in cursor.fetchall()]

            return students, total

        finally:
            cursor.close()


# 使用示例
if __name__ == '__main__':
    # 初始化数据库(会自动检查并创建表)
    with StudentDB() as db:
        # 批量插入示例数据
        batch_students = [
            {
                "testId": "test2023001",
                "testName": "期中考试",
                "siteCode": "SCH001",
                "siteName": "第一中学",
                "studentName": "张三",
                "studentType": 0,
                "gender": 0,
                "studentCode": "20230001",
                "idCard": "110101200001011234"
            },
            {
                "testId": "test2023001",
                "testName": "期中考试",
                "siteCode": "SCH001",
                "siteName": "第一中学",
                "studentName": "李四",
                "studentType": 1,
                "gender": 1,
                "studentCode": "20230002",
                "idCard": "110101200002021234"
            },
            {
                "testId": "test2023001",
                "testName": "期中考试",
                "siteCode": "SCH001",
                "siteName": "第一中学",
                "studentName": "王五",
                "studentType": 0,
                "gender": 0,
                "studentCode": "20230003",
                "idCard": "110101200003031234"
            }
        ]

        # 批量插入
        inserted_count = db.insert_students_batch(batch_students)
        print(f"成功批量插入了 {inserted_count} 条考生记录")

        # 查询验证
        students = db.search_students({"testId": "test2023001"})
        print(f"查询到 {len(students)} 条记录:")
        for student in students:
            print(f"ID: {student['id']}, 姓名: {student['studentName']}, 学号: {student['studentCode']}")
